﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.IO;

public partial class A_RecoverPage : System.Web.UI.Page
{
    public static DataTable ta = new DataTable();//用于存储从数据库中取出来的表格，可以在多个函数中使用，所以使用static
    public static DataView vi = new DataView();  //用于数据绑定
    public static string PageState = "student";

    protected void Page_Load(object sender, EventArgs e)
    {
        btnFirst.Text = "最首页";    //分页处理
        btnPrev.Text = "前一页";
        btnNext.Text = "下一页";
        btnLast.Text = "最后页";

        if (!IsPostBack)
        {
            PageState = "student";
            string sql = "select * from STU_INFO where state='0';";
            ExecuteSQL(sql);

        }
    }

    void ExecuteSQL(string sql)
    {
        //string sql = "select courseid,COURSE_INFO.name as coursename,studentid,STU_INFO.name as studentname,STU_INFO.class as class,mark,SEM_INFO.id as semesterid,SEM_INFO.name as semester from SEM_INFO,CLASS_INFO,COURSE_INFO,STU_INFO where CLASS_INFO.courseid=COURSE_INFO.id and CLASS_INFO.studentid=STU_INFO.id and CLASS_INFO.semesterid=SEM_INFO.id and CLASS_INFO.state='1' order by STU_INFO.id ;";
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();

        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ta.Columns.Clear();
        ta.Rows.Clear();
        da.Fill(ta);
        vi = new DataView(ta);
        dg1.DataSource = vi;
        dg1.DataBind();
        myConn.Close();
    }

    void Execute(string sql)
    {
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();
        myConn.Close();
    }

    public void MyDataGrid_Recover(Object sender, DataGridCommandEventArgs e)
    {
        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;             //获取被删除行的索引
        string sql = "";
        if (PageState == "student")
        { 
            string studentid = e.Item.Cells[2].Text;               //保存主键id
            sql = "update STU_INFO set state='1' where id='" + studentid + "';";
            Execute(sql);
            sql = "update CLASS_INFO set state='1' where studentid='" + studentid + "';";
            Execute(sql);  
        }
        else if (PageState == "teacher")
        {
            string teacherid = e.Item.Cells[2].Text;
            sql = "update TEA_INFO set state='1' where id='" + teacherid + "';";
            Execute(sql);
            sql = "update CLASS_INFO set state='1' where courseid in(select id from COURSE_INFO where teacherid='" + teacherid + "');";
            Execute(sql);
        }
        else if (PageState == "course")
        {
            string courseid = e.Item.Cells[2].Text;               //保存主键id
            sql = "update COURSE_INFO set state='1' where id='" + courseid + "';";
            Execute(sql);
            sql = "update CLASS_INFO set state='1' where courseid ='" + courseid + "';";
            Execute(sql);
        }

        ta.Rows.RemoveAt(index);//删除旧行
        vi = new DataView(ta);                    //重新绑定
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();

        //更新数据库数据
        /*
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand(sql, myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();
         */
    }

    public void MyDataGrid_Delete(Object sender, DataGridCommandEventArgs e)  //datagrid中删除行时执行函数
    {

        int index = dg1.CurrentPageIndex * dg1.PageSize + e.Item.ItemIndex;             //获取被删除行的索引
        string sql = "";

        if (PageState == "student")
        {
            string studentid = e.Item.Cells[2].Text;               //保存主键id
            sql = "delete from STU_INFO where id='" + studentid + "';";
            Execute(sql);
            sql = "delete from CLASS_INFO where studentid='" + studentid + "';";
            Execute(sql);
        }
        else if (PageState == "teacher")
        {
            string teacherid = e.Item.Cells[2].Text;
            sql = "delete from TEA_INFO where id='" + teacherid + "';";
            Execute(sql);
            sql = "delete from CLASS_INFO where courseid in(select id from COURSE_INFO where teacherid='" + teacherid + "');";
            Execute(sql);
        }
        else if (PageState == "course")
        {
            string courseid = e.Item.Cells[2].Text;               //保存主键id
            sql = "delete from COURSE_INFO where id='" + courseid + "';";
            Execute(sql);
            sql = "delete from CLASS_INFO where courseid ='" + courseid + "';";
            Execute(sql);
        }

        ta.Rows.RemoveAt(index);                  //删除旧行

        vi = new DataView(ta);                    //重新绑定
        dg1.EditItemIndex = -1;
        dg1.DataSource = vi;
        dg1.DataBind();

        //更新数据库数据
        /*
        SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
        SqlCommand cmd = new SqlCommand("delete from CLASS_INFO where CLASS_INFO.studentid='" + studentid + "'and CLASS_INFO.semesterid='" + semesterid + "' and CLASS_INFO.courseid='" + courseid + "';", myConn);
        myConn.Open();
        cmd.ExecuteNonQuery();

        myConn.Close();
        */

    }

    private void ShowStats()  //用于分页处理
    {
        lblCurrentIndex.Text = "第 " + (dg1.CurrentPageIndex + 1).ToString() + " 页";
        lblPageCount.Text = "总共 " + dg1.PageCount.ToString() + " 页";
    }

    public void PagerButtonClick(object sender, EventArgs e) //用于分页处理
    {
        string arg = ((LinkButton)sender).CommandArgument.ToString();
        switch (arg)
        {
            case "next":
                if (dg1.CurrentPageIndex < (dg1.PageCount - 1))
                {
                    dg1.CurrentPageIndex += 1;
                }
                break;
            case "prev":
                if (dg1.CurrentPageIndex > 0)
                {
                    dg1.CurrentPageIndex -= 1;
                }
                break;
            case "last":
                dg1.CurrentPageIndex = (dg1.PageCount - 1);
                break;
            default:
                dg1.CurrentPageIndex = System.Convert.ToInt32(arg);
                break;
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();
    }

    public void MyDataGrid_Page(Object source, DataGridPageChangedEventArgs e)  //用于分页处理
    {
        int startIndex;
        startIndex = dg1.CurrentPageIndex * dg1.PageSize;
        dg1.CurrentPageIndex = e.NewPageIndex;
        //BindGrid();
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
        ShowStats();

    }

    protected void LinkButton2_Click(object sender, EventArgs e)//学生基本信息
    {
        PageState = "student";
        string sql = "select * from STU_INFO where STU_INFO.state='0';";
        ExecuteSQL(sql);
        /*
        string courseid = "";
        string studentid = "";
        string semesterid = "";
        foreach (DataRow dr in ta.Rows)
        {
            courseid = (string)dr["courseid"];
            studentid = (string)dr["studentid"];
            semesterid = (string)dr["semesterid"];
            dr.Delete();
            SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
            SqlCommand cmd = new SqlCommand("delete from CLASS_INFO where CLASS_INFO.studentid='" + studentid + "'and CLASS_INFO.semesterid='" + semesterid + "' and CLASS_INFO.courseid='" + courseid + "';", myConn);
            myConn.Open();
            cmd.ExecuteNonQuery();
            myConn.Close();
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
         */
    }
    protected void LinkButton3_Click(object sender, EventArgs e)//教师基本信息
    {
        PageState = "teacher";
        string sql = "select * from TEA_INFO where TEA_INFO.state='0';";
        ExecuteSQL(sql);
        /*
        string courseid = "";
        string studentid = "";
        string semesterid = "";
        foreach (DataRow dr in ta.Rows)
        {
            courseid = (string)dr["courseid"];
            studentid = (string)dr["studentid"];
            semesterid = (string)dr["semesterid"];
            dr.Delete();
            SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
            SqlCommand cmd = new SqlCommand("update CLASS_INFO set state='1' where CLASS_INFO.studentid='" + studentid + "'and CLASS_INFO.semesterid='" + semesterid + "' and CLASS_INFO.courseid='" + courseid + "';", myConn);
            myConn.Open();
            cmd.ExecuteNonQuery();
            myConn.Close();
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
         */
    }
    protected void LinkButton4_Click(object sender, EventArgs e)//课程基本信息
    {
        PageState = "course";
        string sql = "select * from COURSE_INFO where COURSE_INFO.state='0';";
        ExecuteSQL(sql);
        /*
        string courseid = "";
        string studentid = "";
        string semesterid = "";
        foreach (DataRow dr in ta.Rows)
        {
            courseid = (string)dr["courseid"];
            studentid = (string)dr["studentid"];
            semesterid = (string)dr["semesterid"];
            dr.Delete();
            SqlConnection myConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=CSDB");
            SqlCommand cmd = new SqlCommand("update CLASS_INFO set state='1' where CLASS_INFO.studentid='" + studentid + "'and CLASS_INFO.semesterid='" + semesterid + "' and CLASS_INFO.courseid='" + courseid + "';", myConn);
            myConn.Open();
            cmd.ExecuteNonQuery();
            myConn.Close();
        }
        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
         */
    }
    protected void Button1_Click(object sender, EventArgs e)//清空数据
    {
        string sql="";
        if (PageState == "student")
        {
            string studentid = "";
            foreach (DataRow dr in ta.Rows)
            {
                studentid = (string)dr["id"];
                dr.Delete();

                sql = "delete from STU_INFO where id='" + studentid + "';";
                Execute(sql);
                sql = "delete from CLASS_INFO where studentid='" + studentid + "';";
                Execute(sql);
            }
        }
        else if (PageState == "teacher")
        {
            string teacherid = "";
            foreach (DataRow dr in ta.Rows)
            {
                teacherid = (string)dr["id"];
                dr.Delete();

                sql = "delete from TEA_INFO where id='" + teacherid + "';";
                Execute(sql);
                sql = "delete from CLASS_INFO where courseid in(select id from COURSE_INFO where teacherid='" + teacherid + "');";
                Execute(sql);
            }
        }
        else if (PageState == "course")
        {
            string courseid = "";
            foreach (DataRow dr in ta.Rows)
            {
                courseid = (string)dr["id"];
                dr.Delete();

                sql = "delete from COURSE_INFO where id='" + courseid + "';";
                Execute(sql);
                sql = "delete from CLASS_INFO where courseid ='" + courseid + "';";
                Execute(sql);
            }
        }

        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
    }
    protected void Button2_Click(object sender, EventArgs e)//全部还原
    {
        string sql = "";
        if (PageState == "student")
        {
            string studentid = "";
            foreach (DataRow dr in ta.Rows)
            {
                studentid = (string)dr["id"];
                dr.Delete();
                
                sql = "update STU_INFO set state='1' where id='" + studentid + "';";
                Execute(sql);
                sql = "update CLASS_INFO set state='1' where studentid='" + studentid + "';";
                Execute(sql);  

            }
        }
        else if (PageState == "teacher")
        {
            string teacherid = "";
            foreach (DataRow dr in ta.Rows)
            {
                teacherid = (string)dr["id"];
                dr.Delete();

                sql = "update TEA_INFO set state='1' where id='" + teacherid + "';";
                Execute(sql);
                sql = "update CLASS_INFO set state='1' where courseid in(select id from COURSE_INFO where teacherid='" + teacherid + "');";
                Execute(sql);
            }
        }
        else if (PageState == "course")
        {
            string courseid = "";
            foreach (DataRow dr in ta.Rows)
            {
                courseid = (string)dr["id"];
                dr.Delete();

                sql = "update COURSE_INFO set state='1' where id='" + courseid + "';";
                Execute(sql);
                sql = "update CLASS_INFO set state='1' where courseid ='" + courseid + "';";
                Execute(sql);
            }
        }


        vi = new DataView(ta);

        dg1.DataSource = vi;
        dg1.DataBind();
    }
}

      